﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.XSSF.UserModel;
using System.Drawing;

namespace LX.Web.Excel
{
    public class ExcelSheet
    {
        public List<String> Columns { get; set; }
        /// <summary>
        /// 自动列宽
        /// </summary>
        public List<String> AutoSizeColumns { get; set; }
        /// <summary>
        /// 忽略列
        /// </summary>
        public List<String> IgnoreColumns { get; set; }

        public int CurrentRowIndex { get; set; }
        public string Name { get; set; }
        private ISheet sheet { get; set; }
        private List<ExcelCell> cells = new List<ExcelCell>();
        /// <summary>
        /// 头部占行数
        /// </summary>
        private int headerrowline = 1;

        public ExcelSheet()
        {
            init();
        }
        public ExcelSheet(ISheet sheet)
        {
            this.sheet = sheet;
            init();
            CurrentRowIndex = sheet.LastRowNum + 1;
        }

        private void init()
        {
            Columns = new List<string>();
            AutoSizeColumns = new List<string>();
        }

        public int GetNewRowIndex()
        {
            return CurrentRowIndex++;
        }
        public int GetColIndex(string name)
        {
            for (int i = 0; i < Columns.Count; i++)
            {
                var ci = Columns[i];
                if (ci.Contains('|'))
                {
                    if (ci.Split('|').Contains(name))
                    {
                        return i;
                    }
                }
                else
                {
                    if (ci == name)
                    {
                        return i;
                    }
                }
            }
            return -1;
        }

        public void SetHolderValue(string name, string value)
        {
            for (int i = 0; i < CurrentRowIndex; i++)
            {
                var row = sheet.GetRow(i);
                var cellcount = row.LastCellNum;
                for (int j = 0; j < cellcount; j++)
                {
                    var cell = row.GetCell(j);
                    if (cell != null&&cell.CellType== CellType.STRING&&cell.StringCellValue!=null&&cell.StringCellValue.Trim()=="{"+name+"}")
                    {
                        cell.SetCellValue(value);
                    }
                }
            }
        }

        public void AddRow(IExport exportobject, ExcelCellBg bg = ExcelCellBg.Normal)
        {
            var excelitems = exportobject.GetExcelRow();
            int row = GetNewRowIndex();
            foreach (var item in excelitems)
            {
                item.Row = row;
                item.Col = GetColIndex(item.Name);
                if (item.BackBround != ExcelCellBg.Warning&&item.BackBround != ExcelCellBg.Red)
                {
                    item.BackBround = bg;
                }
                if (item.Col != -1)
                {
                    cells.Add(item);
                }
            }
        }

        ICellStyle Getcellstyle(ExcelCell cell, ExcelCellBg bg = ExcelCellBg.Normal)
        {
            ICellStyle cellStyle = sheet.Workbook.CreateCellStyle();            
            //水平对齐  
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;

            //垂直对齐  
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            
            //自动换行  
            //cellStyle.WrapText = true;  
            switch (cell.BackBround)
            {
                case ExcelCellBg.Pass:
                    {
                        //cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                       // cellStyle.FillForegroundColor = HSSFColor.GREEN.index;
                        break;
                    }
                case ExcelCellBg.Warning:
                    {
                        cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                        cellStyle.FillForegroundColor = HSSFColor.RED.index;
                        break;
                    }
                case ExcelCellBg.Group:
                    {
                        cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                        cellStyle.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
                        break;
                    }
                case ExcelCellBg.Sum:
                    {
                        cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                        cellStyle.FillForegroundColor = HSSFColor.GREEN.index;
                        break;
                    }
                case ExcelCellBg.Calculate:
                    {
                        cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                        cellStyle.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
                        break;
                    }
                case ExcelCellBg.Green:
                    {
                        cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                        cellStyle.FillForegroundColor = HSSFColor.GREEN.index;
                        break;
                    }
                case ExcelCellBg.Red:
                    {
                        cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                        cellStyle.FillForegroundColor = HSSFColor.RED.index;
                        break;
                    }
                case ExcelCellBg.Yellow:
                    {
                        cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                        cellStyle.FillForegroundColor = HSSFColor.YELLOW.index;
                        break;
                    }
            }
            return cellStyle;
        }

        private void Rerowspan()
        {
            if (cells.Any())
            {
                var minrow = cells.Min(x => x.Row);
                var maxrow = cells.Max(x => x.Row);
                for (int j = 0; j < cells.Count; j++)
                {
                    var cell = cells[j];
                    if (cell.IsRemoved) continue;
                    for (int n = cell.Row + 1; n <= maxrow; n++)
                    {
                        var ncell = cells.FirstOrDefault(x => x.Row == n && x.Col == cell.Col);
                        if (ncell != null && ncell.MergeVertical && cell.Value.ToString() == ncell.Value.ToString())
                        {
                            cell.Rowspan++;
                            ncell.IsRemoved = true;
                        }
                        else
                        {
                            break;
                        }
                    }
                }
            }
            var removed = cells.Where(x => x.IsRemoved).ToList();
            for (int i = 0; i < removed.Count; i++)
            {
                cells.Remove(removed[i]);
            }
        }

        public void Fill()
        {
            Rerowspan();
            if (cells.Any())
            {
                var minrow = cells.Min(x => x.Row);
                var maxrow = cells.Max(x => x.Row);
                for (int i = minrow; i <= maxrow; i++)
                {
                    var thisrowcells = cells.Where(x => x.Row == i);
                    var row = sheet.CreateRow(i);
                    foreach (var cell in thisrowcells.Where(x => !x.IsRemoved))
                    {
                        ICell excelcell = row.CreateCell(cell.Col);
                        double cd;
                        if (double.TryParse(cell.Value.ToString(),out cd))
                        {
                            excelcell.SetCellValue(cd);
                        }
                        else
                        {
                            excelcell.SetCellValue(cell.Value.ToString());
                        }
                        excelcell.CellStyle = Getcellstyle(cell);
                        
                        if (cell.Rowspan > 0)
                        {
                            CellRangeAddress range = new CellRangeAddress(cell.Row, cell.Row + cell.Rowspan, cell.Col, cell.Col);
                            sheet.AddMergedRegion(range);
                        }
                        if (cell.Colspan > 0)
                        {
                            CellRangeAddress range = new CellRangeAddress(cell.Row, cell.Row, cell.Col, cell.Col + cell.Colspan);
                            sheet.AddMergedRegion(range);
                        }
                    }
                }
            }

            foreach (var c in this.AutoSizeColumns)
            {
                if (this.Columns.Contains(c))
                {
                    sheet.AutoSizeColumn(this.Columns.IndexOf(c));
                }
            }
            
        }

        public void SetHeaderRowLine(int n)
        {
            this.headerrowline = n;
        }

        public List<T> GetRowDatas<T>(ImportHelper<T> helper) where T:class,new()
        {
            List<T> result = new List<T>();
            var lastrow = sheet.LastRowNum;
            if (lastrow < headerrowline) return result;
            var headerrow = sheet.GetRow(0);

            for (var i = headerrowline; i <= lastrow; i++)
            {
                var row = sheet.GetRow(i);
                var colcount = row.LastCellNum;
                List<ImportItem> itemdatas = new List<ImportItem>();
                for (var j = 0; j < colcount; j++)
                {
                    ImportItem item = new ImportItem();
                    
                    if (this.Columns != null && this.Columns.Any())
                    {
                        item.Name = this.Columns[j];
                        item.Row = i+1;
                        ICell cell = row.GetCell(j);

                        fillitem(item, cell);
                    }
                    else
                    {
                        item.Name = headerrow.GetCell(j).StringCellValue;
                        item.Row = i+1;
                        ICell cell = row.GetCell(j);
                        fillitem(item, cell);
                    }
                    if (this.IgnoreColumns == null || !this.IgnoreColumns.Any() || !this.IgnoreColumns.Contains(item.Name))
                    {
                        itemdatas.Add(item);
                    }
                }
                T titem = new T();
                helper.SetData(titem);
                helper.Fill(itemdatas);
                //titem.Fill(itemdatas);
                result.Add(titem);
            }

            return result;

        }

        private void fillitem(ImportItem item,ICell cell)
        {
            if (cell == null) return;
            if (cell.CellType == CellType.STRING)
            {
                item.Value = cell.StringCellValue;
            }
            else if (cell.CellType == CellType.NUMERIC)
            {
                item.Value = cell.NumericCellValue;
            }
            else if (cell.CellType == CellType.BOOLEAN)
            {
                item.Value = cell.BooleanCellValue;
            }
        }
    }
}
